﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectStr = "Data Source=.;Initial Catalog=test;Integrated Security=True";
            SqlConnection Sqlconn = new SqlConnection(connectStr); //打开连接
            Sqlconn.Open();
            //为连接指定Command对象--查询数据
            SqlCommand thisCommand = Sqlconn.CreateCommand();
            thisCommand.CommandText = "select userName,userID,phone,address from t_user";
            //为指定的command对象执行DataReader
            SqlDataReader thisSqlDataReader = thisCommand.ExecuteReader();
            //循环读取查询到的数据值
            while (thisSqlDataReader.Read())
            {
                Console.WriteLine("{0} {1} {2} {3}", thisSqlDataReader["userName"],
               thisSqlDataReader["userID"], thisSqlDataReader["phone"], thisSqlDataReader["address"]);
            }
            //关闭读取
            thisSqlDataReader.Close();
            String sqlInsert = "insert into t_user(userName,userID,phone,address)values(@NAME, @ID, @PHONE, @ADDRESS)";
            SqlCommand cmdInsert = new SqlCommand(sqlInsert, Sqlconn);
            SqlParameter para1 = new SqlParameter("@NAME", "liu");
            cmdInsert.Parameters.Add(para1);
            SqlParameter para2 = new SqlParameter("@ID", "007");
            cmdInsert.Parameters.Add(para2);
            SqlParameter para3 = new SqlParameter("@PHONE", "13502296");
            cmdInsert.Parameters.Add(para3);
            SqlParameter para4 = new SqlParameter("@ADDRESS", "浙江杭州");
            cmdInsert.Parameters.Add(para4);
            //对连接执行 Transact-SQL 语句并返回受影响的行数
            int res = cmdInsert.ExecuteNonQuery();
            Console.WriteLine(res == 0 ? "插入数据失败！" : "插入数据成功！");
            string sqlUpdate = "update t_user set userName=@NAME, address=@ADDRESS where userID = @ID";
            SqlCommand cmdUpdate = new SqlCommand(sqlUpdate, Sqlconn);
            cmdUpdate.Parameters.AddWithValue("@ID", "007");
            cmdUpdate.Parameters.AddWithValue("@NAME", "qian"); cmdUpdate.Parameters.AddWithValue ("@ADDRESS", "浙江宁波");
            res = cmdUpdate.ExecuteNonQuery();
            Console.WriteLine(res == 0 ? "更新数据失败！" : "更新数据成功！");
            string sqlDelete = "DELETE FROM t_user WHERE userID=@ID";
            SqlCommand cmdDelete = new SqlCommand(sqlDelete, Sqlconn);
            cmdDelete.Parameters.AddWithValue("@ID", "007");
            res = cmdDelete.ExecuteNonQuery();
            Console.WriteLine(res == 0 ? "删除数据失败！" : "删除数据成功！");
            //关闭连接
            Sqlconn.Close();
        }
    }
}
